#!/usr/bin/env python
# coding:utf-8
import sqlite3


class Database:
    conn = None
    cursor = None
    db_name = ""

    def __init__(self, database_name: str):
        self.db_name = database_name
        self.conn = sqlite3.connect(f"{database_name}.db")
        self.cursor = self.conn.cursor()

    def execute_safe_sql(self, sql: str):
        result = []
        try:
            result = self.cursor.execute(sql).fetchall()
        except Exception as e:
            self.conn.rollback()
            print(f"rollback sql> {sql}")
            raise e
        self.conn.commit()
        return result

    def __del__(self):
        print(f"Database operator object \"{self.db_name}\" has be destoryed!")
        self.conn.close()


class StockDatabase(Database):
    def __init__(self, database_name):
        super().__init__(database_name)
        self.execute_safe_sql('''CREATE TABLE IF NOT EXISTS STOCK_LIST
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            SYMBOL          TEXT       UNIQUE NOT NULL,
            NAME            TEXT       NOT NULL,
            ENGNAME         TEXT       NOT NULL,
            REG             TEXT       NOT NULL,
            DISABLE         INTEGER    DEFAULT 0);''')

    def add_stock(self, symbol: str, name: str, engname: str, reg: str):
        self.execute_safe_sql(f"""
            INSERT OR IGNORE INTO STOCK_LIST(SYMBOL,NAME,ENGNAME,REG) VALUES ("{symbol}","{name}","{engname}","{reg}");
        """)


if __name__ == "__main__":
    try:
        db_name = input("The Database name>")
        ndb = Database(db_name)
        while True:
            query_sql = input("SQL>")
            try:
                result = ndb.execute_safe_sql(query_sql)
                print("-" * 30)
                print(result)
                print("-" * 30)
            except Exception as e:
                print(e)
            print("=" * 30)
    except KeyboardInterrupt:
        exit()
